alter table ClientPackageChoice
add OverrideChoice varchar(100) null
go
ALTER PROCEDURE [dbo].[spContract_SetBooked]
(
	@ContractId int,
	
	@ChoiceIdList varchar(max) = null,
	@OverrideChoiceList varchar(max) = null
)
AS
BEGIN
	set nocount on
	set xact_abort on
		
	declare @ContactId int, @PackageId int, @TravelId int
	
	select @ContactId = ContactId, @PackageId = PackageId
	from [Contract] (nolock)
	where ContractId = @ContractId
	
	select @TravelId = TravelId 
	from ClientPackage (nolock)
	where PackageId = @PackageId
		and ClientId = @ContactId
		
	begin transaction
		update ClientPackage
		set [Status] = 'BOOKED'
		where TravelId = @TravelId
		
		if @ChoiceIdList is not null
			update ClientPackageChoice
			set OverrideChoice = vwOverride.Item
			from dbo.fnStringSplit(@ChoiceIdList, ';') as vwId inner join dbo.fnStringSplit(@OverrideChoiceList, ';') as vwOverride on vwId.IdItem = vwOverride.IdItem
			where ClientPackageChoice.TravelId = @TravelId
				and ClientPackageChoice.ChoiceId = Convert(int, vwId.Item)
	commit transaction
END
go
create PROCEDURE [dbo].[spContract_SetCanceled]
(
	@ContractId int
)
AS
BEGIN
	set nocount on
	set xact_abort on
		
	declare @ContactId int, @PackageId int, @TravelId int
	
	select @ContactId = ContactId, @PackageId = PackageId
	from [Contract] (nolock)
	where ContractId = @ContractId
	
	select @TravelId = TravelId
	from ClientPackage (nolock)
	where ClientId = @ContactId
		and PackageId = @PackageId
	
	update ClientPackage
	set [Status] = 'CANCELED' 
	where TravelId = @TravelId
END
go